﻿IF EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'OTResumenMultipleRep')
	BEGIN
		DROP  Procedure  OTResumenMultipleRep
	END
GO

CREATE PROCEDURE [dbo].[OTResumenMultipleRep]
	(	
		@EmpresaId		AS INT,		
		@NumeroOT		AS VARCHAR(30) = NULL,
		@EstadoId		AS INT = NULL,
		@FechaInicial	AS DATETIME = NULL,
		@FechaFinal		AS DATETIME = NULL
	)	
AS

BEGIN	
	SET NOCOUNT ON

	CREATE TABLE #OTIds			(Id INT)
	CREATE TABLE #OTEntregaIds	(Id INT)

	IF @FechaInicial IS NULL	
		INSERT INTO #OTIds
		SELECT Id 
		FROM Lit_OT
		WHERE EmpresaId = @EmpresaId
		  AND NumeroOT = ISNULL(@NumeroOT, NumeroOT)
		  AND EstadoId = ISNULL(@EstadoId, EstadoId)
	ELSE
		INSERT INTO #OTIds
		SELECT Id 
		FROM Lit_OT
		WHERE EmpresaId = @EmpresaId
		  AND NumeroOT = ISNULL(@NumeroOT, NumeroOT)
		  AND EstadoId = ISNULL(@EstadoId, EstadoId)
		  AND FechaInicial BETWEEN @FechaInicial AND @FechaFinal
	
	INSERT INTO #OTEntregaIds
	SELECT	Id
	FROM	Lit_OTEntrega
	WHERE	OTId IN (SELECT Id FROM #OTIds)	

	DECLARE @OTs Table
			(
			EntregaId	INT,
			NumeroOT	VARCHAR(30),
			Entrega	INT,
			EmpresaNombre VARCHAR (50),
			FechaInicial DATETIME,
			FechaFinal DATETIME,
			Estado VARCHAR(20),
			Cantidad decimal(10, 2),
			CantidadProducida decimal(10, 2),
			MontoManoDeObra decimal(10, 2),
			MontoMateriales decimal(10, 2),
			MontoGastosDirectos decimal(10, 2),
			MontoGastosIndirectos decimal(10, 2),
			MontoMaquinasUtilizadas decimal(10, 2))
	
	DECLARE @OTDetalle				TABLE (EntregaId INT, Total DECIMAL(10,2))
	DECLARE @OTDetalleSum			TABLE (EntregaId INT, Total DECIMAL(10,2))
	DECLARE @OTMateriales			TABLE (EntregaId INT, Total	DECIMAL(10,2))
	DECLARE @OTGastosDirectos		TABLE (EntregaId INT, Total	DECIMAL(10,2))
	DECLARE @OTGastosIndirectos		TABLE (EntregaId INT, Total	DECIMAL(10,2))
	DECLARE @OTGastosIndirectosSum	TABLE (EntregaId INT, Total	DECIMAL(10,2))
	DECLARE @OTMaquinasUtilizadas	TABLE (EntregaId INT, Total	DECIMAL(10,2))

	-- OT Mano de Obra Utilizada
	INSERT INTO @OTDetalle
	SELECT o.OTEntregaId, SUM(  (ISNULL(HorasRegular,0)* ISNULL(CostoOperarioxHora,0)) 
							+(ISNULL(HorasExtra,0)*ISNULL(CostoOperarioxHora,0)*1.5)
							+(ISNULL(HorasDoble,0)*ISNULL(CostoOperarioxHora,0)*2.0)) AS Total
	FROM Lit_OTDetalle o
	INNER JOIN Lit_Operario op ON o.operarioId = op.id
	INNER JOIN Lit_OTEntrega e ON e.Id = o.OTEntregaId
	WHERE e.OTId IN (SELECT Id FROM #OTIds)	  
	GROUP BY o.OTEntregaId
	UNION
	SELECT o.OTEntregaId, SUM(  (ISNULL(HorasRegular,0)* ISNULL(CostoAyudantexHora,0))
							+(ISNULL(HorasExtra,0)*ISNULL(CostoAyudantexHora,0)*1.5) 
							+(ISNULL(HorasDoble,0)*ISNULL(CostoAyudantexHora,0)*2.0)) AS Total
	FROM Lit_OTDetalle o
	INNER JOIN Lit_Operario op ON o.ayudanteId = op.id
	WHERE o.OTEntregaId IN (SELECT Id FROM #OTEntregaIds)
	GROUP BY o.OTEntregaId
	
	INSERT INTO @OTDetalleSum
	SELECT EntregaId, SUM(Total)
	FROM @OTDetalle
	GROUP BY EntregaId

	-- OT Materiales Utilizados
	INSERT INTO @OTMateriales
	SELECT m.OTEntregaId, SUM(CostoArticuloTotal)
	FROM Lit_OTMaterial m
	INNER JOIN Articulo a ON m.ArticuloId = a.Art_id
	WHERE m.OTEntregaId IN (SELECT Id FROM #OTEntregaIds)
	AND a.Emp_Id = @empresaId
	GROUP BY m.OTEntregaId

	-- OT Gastos Directos
	INSERT INTO @OTGastosDirectos
	SELECT otg.OTEntregaId, SUM(otg.Monto)
	FROM Lit_OTGasto otg
	INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
	WHERE otg.OTEntregaId IN (SELECT Id FROM #OTEntregaIds)
	AND g.Tipo = 'D'
	GROUP BY otg.OTEntregaId

	-- OT Gastos Indirectos
	INSERT INTO @OTGastosIndirectos
	SELECT otg.OTEntregaId, otg.Monto
	FROM Lit_OTGasto otg
	INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
	WHERE otg.OTEntregaId IN (SELECT Id FROM #OTEntregaIds)  
	AND g.Tipo = 'I'
	UNION
	SELECT Id, ISNULL(ManoObraIndirecta,0)
	FROM Lit_OTEntrega
	WHERE Id IN (SELECT Id FROM #OTEntregaIds)
	AND ManoObraIndirecta IS NOT NULL
	AND ManoObraIndirecta > 0

	INSERT INTO @OTGastosIndirectosSum
	SELECT EntregaId, SUM(Total)
	FROM @OTGastosIndirectos
	GROUP BY EntregaId

	-- OT Maquinas Utilizadas
	INSERT INTO @OTMaquinasUtilizadas
	SELECT o.OTEntregaId, SUM(CostoMaquina)
	FROM Lit_OTDetalle o
	WHERE OTEntregaId IN (SELECT Id FROM #OTEntregaIds) 
	GROUP BY o.OTEntregaId

	INSERT INTO @OTs 
	SELECT	ote.Id,
			o.NumeroOT, 
			ote.Orden AS Entrega,
			e.Nombre AS EmpresaNombre, 
			ote.FechaInicial, 
			ote.FechaFinal, 
		    s.Estado, 
			ISNULL(ote.UnidadesSolicitadas,0), 
			ISNULL(ote.UnidadesProducidas,0),
			ISNULL(mo.Total, 0),
			ISNULL(m.Total,0),
			ISNULL(gd.Total, 0),
			ISNULL(gi.Total, 0),
			ISNULL(mu.Total, 0)
	FROM lit_OT o 
	INNER JOIN ld_general..empresas e ON o.empresaId = e.id
	INNER JOIN Lit_OTEntrega ote ON ote.OTId = o.Id
	INNER JOIN Lit_Estado s ON s.Id = o.EstadoId
	LEFT JOIN @OTDetalleSum mo ON mo.EntregaId = ote.Id
	LEFT JOIN @OTMateriales m ON m.EntregaId = ote.Id
	LEFT JOIN @OTGastosDirectos gd ON gd.EntregaId = ote.Id
	LEFT JOIN @OTGastosIndirectosSum gi ON gi.EntregaId = ote.Id
	LEFT JOIN @OTMaquinasUtilizadas mu ON mu.EntregaId = ote.Id
	WHERE o.Id IN (SELECT Id FROM #OTIds)
	
	SELECT *, MontoManoDeObra + MontoMateriales + MontoGastosDirectos + MontoGastosIndirectos + MontoMaquinasUtilizadas As MontoTotal
	FROM @OTs
           	          
	SET NOCOUNT OFF
END
GO